package dao;

import bean.HygInfo;
import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李夏
 * @version 1.0
 */
public class HygInfoMapper {
    public static List<HygInfo> queryHygByBuild(Integer i) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select * from hyg where delete_flag=0 and build_id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, i);
        resultSet = preparedStatement.executeQuery();
        List<HygInfo> hygInfos = new ArrayList<>();
        while (resultSet.next()){
            HygInfo hygInfo=new HygInfo();
            hygInfo.setHygId(resultSet.getInt("hyg_id"));
            hygInfo.setBuildId(resultSet.getInt("build_id"));
            hygInfo.setDorNumber(resultSet.getInt("dor_number"));
            hygInfo.setCheckSore(resultSet.getInt("check_sore"));
            hygInfo.setCheckPerson(resultSet.getString("check_person"));
            hygInfo.setDoId(resultSet.getInt("do_id"));
            hygInfo.setHygDes(resultSet.getString("hyg_des"));
            hygInfo.setDeleteFlag(resultSet.getInt("delete_flag"));
            hygInfos.add(hygInfo);
        }
        return hygInfos;
    }
//宿管/学生/教师/管理员获取某楼栋卫生信息条数
    public static Integer getHygOneBuild(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as hygiene_info_count  \n"+
                "from hyg  \n"+
                "where delete_flag=0 and build_id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, buildId);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("hygiene_info_count");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员获取所有楼栋的卫生信息条数
    public static Integer queryHygAllCount() throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hyg \n"+
                "from hyg  \n"+
                "where delete_flag=0";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hyg");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
}
